import re

import pymysql


class connect_sql(object):
    def __init__(self, kuming, biaoming):
        self.kuming = kuming
        self.biaoming = biaoming
        self.host = "192.168.1.192"
        self.user = "root"
        self.port = 3306
        self.passwd = "Hengde123"
        self.charset = 'utf8'

    def table_exists(self, con, table_name):  # 这个函数用来判断表是否存在
        sql = "show tables;"
        con.execute(sql)
        tables = [con.fetchall()]
        table_list = re.findall('(\'.*?\')', str(tables))
        table_list = [re.sub("'", '', each) for each in table_list]
        if table_name in table_list:
            return 1  # 存在返回1
        else:
            return 0  # 不存在返回0

    def insert_mysql_machine(self, time, machine_code, mode_code, set_value, statistical_value, actual_temperature_value, curve_value):
        mysql = pymysql.connect(host=self.host, user=self.user, port=self.port, passwd=self.passwd, db=self.kuming, charset=self.charset)
        cursor = mysql.cursor()

        # 英文字母转小写
        modo_code = machine_code
        modo_code = modo_code.replace('-', '_')
        if self.table_exists(cursor, modo_code) != 1:
            cursor.execute("create table {} like cracking_machine_data_a10;".format(modo_code))

        cursor.execute('use {}'.format(self.kuming))
        sql = "insert into {} (time, machine_code, mode_code, set_value, statistical_value, actual_temperature_value, curve_value) " \
              "VALUES(%s,%s,%s,%s,%s,%s,%s)".format(self.biaoming)
        cursor.execute(sql, (time, machine_code, mode_code, set_value, statistical_value, actual_temperature_value, curve_value))
        mysql.commit()
        cursor.close()
        mysql.close()
